package cn.jdy.audit.service;
import java.util.List;
import java.util.Map;

import org.hibernate.SQLQuery;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import cn.jdy.audit.entity.UrlRecord;
import cn.jdy.framework.db.hibernate.BaseDao;
import cn.jdy.framework.log.BaseLog;
import cn.jdy.framework.util.StringUtil;
import cn.jdy.platform.service.ConfService;
/**
 * @author daihu
 * @title : 产品操作实现
 */
@Service
public class UrlRecordService extends BaseDao<UrlRecord> {

	@Autowired
	private ConfService confService;

	/**
	 * 按条件查询
	 * 
	 * @return
	 */
	public List<UrlRecord> query(UrlRecord urlrecord, String... date) {
		if (urlrecord == null)
			return super.findAll();
		if (urlrecord.getiDisplayLength() > 0) {
			return super.find(createFilter(urlrecord, date),
					urlrecord.getiDisplayStart(), urlrecord.getiDisplayLength());
		}
		return super.find(createFilter(urlrecord, date));
	}

	public Long count(UrlRecord urlrecord, String... date) {
		return super.count(createFilter(urlrecord, date));
	}

	/**
	 * 拼装条件
	 * 
	 * @param page
	 * @return
	 */
	private DetachedCriteria createFilter(UrlRecord urlrecord, String... date) {
		DetachedCriteria dc = DetachedCriteria.forClass(entityClass);
		if(!StringUtil.isNullOrEmpty(urlrecord.getKeywords())) { //后台管理用到这个地方
      if(!StringUtil.isNullOrEmpty(urlrecord.getKeyType())) {
        dc.add(Restrictions.like(urlrecord.getKeyType(), urlrecord.getKeywords().trim(),MatchMode.ANYWHERE));
      }else
        //TODO 添加模糊搜索
        ;
    }	
		if (date.length > 1 && !StringUtil.isNullOrEmpty(date[0])
				&& !StringUtil.isNullOrEmpty(date[1])) {
			dc.add(Restrictions.between("createTime", date[0], date[1]));
		}
		
		if (!StringUtil.isNullOrEmpty(urlrecord.getUrl_host())) {
			dc.add(Restrictions.like("url_host", urlrecord.getUrl_host(),MatchMode.ANYWHERE));
		}
		if (!StringUtil.isNullOrEmpty(urlrecord.getUser_ip())) {
			dc.add(Restrictions.like("user_ip", urlrecord.getUser_ip(),MatchMode.ANYWHERE));
		}
		dc.addOrder(Order.desc("id"));
		return dc;
	}

	/**
	 * 
	 * @param urlrecord
	 * @return
	 */
	public List<UrlRecord> querypage(UrlRecord urlrecord) {
		return super.find(createFilter(urlrecord),
				urlrecord.getPageNo(), 4);
	}

	/**
	 * 查询热门产品
	 * 
	 * @return
	 */
	public List<UrlRecord> queryHot() {
		DetachedCriteria dc = DetachedCriteria.forClass(entityClass);
		dc.addOrder(Order.desc("readTimes"));
		return super.find(dc, 0, 5);

	}

	/**
	 * 按照类型查询 产品
	 * 
	 * @return
	 */
	public List<UrlRecord> findbytype(Long columnid, int firstResult,
			int maxResults, boolean isImg) {
		DetachedCriteria dc = DetachedCriteria.forClass(entityClass);
		if (null != columnid) {
			dc.add(Restrictions.eq("columnId", columnid));
		}
		if (isImg) {// 是否有封面图片
			dc.add(Restrictions.ne("imgUrl", ""));
		}
		dc.addOrder(Order.desc("createTime"));
		return super.find(dc, firstResult, maxResults);
	}

	/**
	 * 按照大分类 查询产品
	 * 
	 * @return
	 */
	public List<UrlRecord> findbyPid(Long pid, int firstResult, int maxResults,
			boolean isImg) {
		DetachedCriteria dc = DetachedCriteria.forClass(entityClass);
		dc.createAlias("cv", "cv");
		if (isImg) {// 是否有封面图片
			dc.add(Restrictions.ne("imgUrl", ""));
		}
		if (null != pid) {
			dc.add(Restrictions.eq("cv.pid", pid));
		}
		dc.addOrder(Order.desc("createTime"));
		return super.find(dc, firstResult, maxResults);
	}

	public List<Map> querytop(String url_b,String url_e,String url_host,String user_ip) {
		String sql="";
		try {
			if((url_b!="")&&(url_e!="")){
				 sql = "select url_host,count(url_host) aa from url_record where unix_timestamp('"+url_b+"')<add_time "
				 		+ "and unix_timestamp('"+url_e+"') >add_time";
			}else{
				 sql = "select url_host,count(url_host) aa from url_record where unix_timestamp(date_format(now(), '%Y-%m-%d 00:00:00'))<add_time"
				 		+ " and unix_timestamp(date_format(DATE_ADD(now(),INTERVAL 1 DAY), '%Y-%m-%d 00:00:00'))>add_time ";
			}	
			if(url_host!=""){
				sql = sql + " and url_host like '%"+url_host+"%' ";
			}
			if(user_ip!=""){
				sql = sql + " and user_ip like '%"+user_ip+"%' ";
			}
			sql = sql + " GROUP BY url_host ORDER BY aa desc limit 20";
			System.out.println(sql+"11111111");
			SQLQuery sqlQuery = em().createSQLQuery(sql);
			// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
			sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			return sqlQuery.list();
		} catch (Exception e) {
			BaseLog.e(this.getClass(), "url查询top20异常！", e);
		}
		return null;
	}
	
	public int queryCountTop() {
		int count = 0;
		try {
			String sql = "select url_host,count(url_host) aa from url_record "
					+"GROUP BY url_host ORDER BY aa desc limit 20";
			SQLQuery sqlQuery = em().createSQLQuery(sql);
			List list = sqlQuery.list();
			if(!(list == null || list.size() == 0)){
				count = list.size();
			}
		} catch (Exception e) {
			BaseLog.e(this.getClass(), "url统计top20异常！", e);
		}
		return count;
	}
	
	public void updateSql(){
		String sql="update url_record set createTime=FROM_UNIXTIME(add_time) where createTime is NULL";
		SQLQuery sqlQuery = em().createSQLQuery(sql);
		sqlQuery.executeUpdate();
	}
}
